package com.hb.trade.api.routine.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpResponse;
import com.hb.core.pojo.enums.FirstReviewItemTypeEnum;
import com.hb.core.pojo.project.evaluation.dto.EvaluationThirdDTO;
import com.hb.core.pojo.project.evaluation.entity.EvaluationCommitteeMembers;
import com.hb.core.pojo.project.evaluation.entity.EvaluationSecond;
import com.hb.core.pojo.project.file.dto.*;
import com.hb.core.pojo.project.section.dto.BidSectionDTO;
import com.hb.trade.client.project.EvaluationCommitteeClient;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.fzzn.core.file.entity.UploadEntity;
import org.fzzn.core.file.service.PersistenceService;
import org.fzzn.core.model.msg.Message;
import org.fzzn.core.util.Enums;
import org.fzzn.core.web.util.SpringUtils;

import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Member;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * describe:
 *
 * @author zhongdl
 * @date 2022/04/19
 */
@Slf4j
public class ReviewItemExcelUtil {
    /**
     * @param reviewItemSecondDto: 生成初步评审表格
     * @Description:
     * @Author: zhongdl
     * @Date:2022/5/13 16:02
     * @return: {@link java.lang.String }
     **/
    public static String preliminaryExcel(ReviewItemSecondDto reviewItemSecondDto) {
        ByteArrayOutputStream bos = null;
        InputStream in = null;
        PersistenceService persistenceService = SpringUtil.getBean(PersistenceService.class);
        try (HSSFWorkbook workbook = new HSSFWorkbook();
        ) {
            String sheetName = "初步评审-" + reviewItemSecondDto.getSecondReviewItemName() + "汇总表";
            int expertRowNum = 1;
            List<EvaluationCommitteeMembers> membersList = reviewItemSecondDto.getMembersList();
            if (CollUtil.isNotEmpty(membersList)){
                expertRowNum = (int) Math.ceil(membersList.size() / 7.0);
            }


            //生成一个表格，设置表格名称
            HSSFSheet sheet = workbook.createSheet(reviewItemSecondDto.getSecondReviewItemName());

            List<ReviewItemThirdDto> reviewItemThirdDtos = reviewItemSecondDto.getReviewItemThirdDtos();
            //总宽度
            int totalWidth = reviewItemThirdDtos.size() + 3;
            //结束列
            int endWidth = Math.max(totalWidth, 22);

            HSSFRow row1 = sheet.createRow(0);
            row1.createCell(0).setCellValue(sheetName);
            //第一列
            sheet.setColumnWidth(0, (int) ((3.74 + 0.72) * 256));


           //第二列
           sheet.setColumnWidth(1, (int) ((22.62 + 0.72) * 256));

            row1.setHeight((short) (39.95 *20));
            int columnwith = (int)(4.62 * 256 + 184);
            for (int i = 2; i < endWidth; i++) {
              sheet.setColumnWidth(i, columnwith);
            }
            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            // 行和列都是从0开始计数，且起始结束都会合并
            // 这里是合并excel中日期的两行为一行
             createCellRangeAddress(sheet,0, 0, 0, (endWidth - 1) );

            int average = endWidth / 2;
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 16);  //字体大小
            font.setFontName("宋体");
            //第二行
            HSSFRow row2 = sheet.createRow(1);
            row2.createCell(0).setCellValue("招标项目名称:" + reviewItemSecondDto.getProjectName());
            HSSFCellStyle cellStyle1 = row2.getCell(0).getCellStyle();
            cellStyle1.setFont(font);
            cellStyle1.setAlignment(HorizontalAlignment.CENTER); // 居中

            createCellRangeAddress(sheet,1, 1, 0,  average - 1 );
            row2.createCell(average).setCellValue("招标项目编号:" + reviewItemSecondDto.getProjectCode());
            createCellRangeAddress(sheet,1, 1, average ,  (endWidth - 1) );

            //第三行
            HSSFRow row3 = sheet.createRow(2);
            row3.createCell(0).setCellValue("标段(包)名称:" + reviewItemSecondDto.getSectionName());
            createCellRangeAddress(sheet,2, 2, 0,  average - 1  );
            row3.createCell(average).setCellValue("标段(包)编号:" + reviewItemSecondDto.getSectionCode());
             createCellRangeAddress(sheet,2, 2, average,  (endWidth - 1));


            //第四行
            HSSFRow row4 = sheet.createRow(3);
              //第一列
            row4.createCell(0).setCellValue("序号");
            createCellRangeAddress(sheet,3, 4, 0,  0 );

            //第二列
            row4.createCell(1).setCellValue("投标人");
            createCellRangeAddress(sheet,3, 4, 1,  1 );

            //第三列
            row4.createCell(2).setCellValue("评审因素");
            createCellRangeAddress(sheet,3, 3, 2,   (endWidth - 1) );

            //第五行
            HSSFRow row5 = sheet.createRow(4);
            int reviewItemThirdSize = reviewItemThirdDtos.size();
            int reviewItemThirdFirstRow = 2;
            int reviewItemThirdColumnWith = (endWidth - 2) / (reviewItemThirdSize + 1);
            for (int i = 0; i < reviewItemThirdSize ; i++) {
                ReviewItemThirdDto reviewItemThirdDto = reviewItemThirdDtos.get(i);
                row5.createCell(reviewItemThirdFirstRow).setCellValue(reviewItemThirdDto.getThirdReviewItemName());
                int endCellColum = reviewItemThirdFirstRow + reviewItemThirdColumnWith - 1;
                if (endCellColum > reviewItemThirdFirstRow){
                    createCellRangeAddress(sheet,4, 4, reviewItemThirdFirstRow, endCellColum );
                }


                reviewItemThirdFirstRow += (reviewItemThirdColumnWith);
            }
            row5.createCell(reviewItemThirdFirstRow).setCellValue("是否合格");
            createCellRangeAddress(sheet,4, 4, reviewItemThirdFirstRow, (endWidth - 1) );

            //开始行第5行
            int orgFirstRow = 5;
            List<EvaluationSecond> orgIdList = CollUtil.list(false);
            if (CollUtil.isNotEmpty(reviewItemSecondDto.getOrgIdList())){
                orgIdList = reviewItemSecondDto.getOrgIdList();
            }
            int orgSize = orgIdList.size();
            //评审点
            List<EvaluationThirdDTO> evaluationThirdList = CollUtil.isEmpty(reviewItemSecondDto.getEvaluationThirdDTOS()) ? CollUtil.list(false): reviewItemSecondDto.getEvaluationThirdDTOS();
            for (int i = 0; i < orgSize; i++) {
                EvaluationSecond evaluationSecond = orgIdList.get(i);
                HSSFRow row = sheet.createRow(orgFirstRow);
                row.createCell(0).setCellValue(i + 1);
                row.createCell(1).setCellValue(evaluationSecond.getOrgName());
                //初级评审只有定性
                reviewItemThirdFirstRow = 2;
                boolean totalFlag = true;
                for (ReviewItemThirdDto reviewItemThirdDto : reviewItemThirdDtos) {
                    //获得当前公司的当前评审点
                    List<Integer> approveFlag = evaluationThirdList.stream().filter(evaluationThird -> evaluationThird.getOrgId() != null && evaluationThird.getOrgId().equals(evaluationSecond.getOrgId()) &&
                            evaluationThird.getThirdReviewItemId().equals(reviewItemThirdDto.getId())).map(EvaluationThirdDTO::getApproveFlag).collect(Collectors.toList());
                    row.createCell(reviewItemThirdFirstRow).setCellValue(approveFlag.contains(2) ? "X" : "√");
                    int endCellColum = reviewItemThirdFirstRow + reviewItemThirdColumnWith - 1;
                    if (endCellColum > reviewItemThirdFirstRow) {
                        createCellRangeAddress(sheet, orgFirstRow, orgFirstRow, reviewItemThirdFirstRow, endCellColum);
                    }
                    reviewItemThirdFirstRow += (reviewItemThirdColumnWith);
                    if (!totalFlag) {
                        continue;
                    }
                    totalFlag = !approveFlag.contains(2);

                }


                row.createCell(reviewItemThirdFirstRow).setCellValue(totalFlag ? "√" : "X");
                createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, reviewItemThirdFirstRow, (endWidth - 1 ));
                orgFirstRow++;
            }

            //设置备注签子
            HSSFRow row6 = sheet.createRow(orgFirstRow);
            row6.createCell(0).setCellValue("备注:");
            createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, 0,   (endWidth - 1));

            orgFirstRow++;
            //设置专家签子
            HSSFRow row7 = sheet.createRow(orgFirstRow);
            row7.createCell(0).setCellValue("专家签字：");
            createCellRangeAddress(sheet,orgFirstRow, expertRowNum > 1 ? (orgFirstRow + expertRowNum) : orgFirstRow, 0,   (endWidth - 1));
            if (expertRowNum > 1){
                orgFirstRow += expertRowNum;
            }

            //设置边框
            setFrame(3,orgFirstRow,endWidth,sheet);
            orgFirstRow++;
            //设置注释
            HSSFRow row8 = sheet.createRow(orgFirstRow);
            row8.createCell(0).setCellValue("注：合格打√，不合格打×");
            createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, 0,   average);

            row8.createCell(average + 1).setCellValue("日期:" + DateUtil.format(new Date(),new SimpleDateFormat("yyyy-MM-dd")));

           createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, average + 1, endWidth - 1);

            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] brray = bos.toByteArray();
            in = new ByteArrayInputStream(brray);
            UploadEntity uploadEntity = new UploadEntity();
            String url = persistenceService.updateToOss(uploadEntity, in, sheetName + ".xlsx");
            in.close();
            bos.close();
           log.info("生成的url{}" + url);
           return url;

        } catch (IOException e) {
            e.printStackTrace();


        }finally {
           if (bos != null){
               try {
                   bos.close();
               } catch (IOException e) {
                   e.printStackTrace();
               }
           }
           if (in != null){
               try {
                   in.close();
               } catch (IOException e) {
                   e.printStackTrace();
               }
           }
        }
        return null;
    }
    /**
     * @param sheet:excelsheet对象
     * @param firstRow:开始行
     * @param lastRow:结束行
     * @param firstCol:开始列
     * @param lastCol:结束列
     * @Description:增加合并列分页对象(参数的索引都是从0开始)
     * @Author: zhongdl
     * @Date:2022/5/13 16:08

     **/
    public static void createCellRangeAddress(HSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol){
        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
        // 行和列都是从0开始计数，且起始结束都会合并
        // 这里是合并excel中日期的两行为一行
        CellRangeAddress cellAddresses = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(cellAddresses);
    }

    /**
     * @param sheet:excelsheet对象
     * @param firstRow:开始行
     * @param lastRow:结束行
     * @param firstCol:开始列
     * @param lastCol:结束列
     * @Description:增加合并列分页对象(参数的索引都是从0开始)
     * @Author: zhongdl
     * @Date:2022/5/13 16:08

     **/
    public static void createCellRangeAddress(XSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol){
        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
        // 行和列都是从0开始计数，且起始结束都会合并
        // 这里是合并excel中日期的两行为一行
        CellRangeAddress cellAddresses = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(cellAddresses);
    }
    /**
     * @param starRow:开始行(从0开始)
     * @param endRow: 开始行(从0开始计算)
     * @param endWith: 开始行(从1开始计算)
     * @param hssfSheet:sheet页
     * @Description:设置excel边框
     * @Author: zhongdl
     * @Date:2022/4/24 11:34
     **/
    public static void setFrame(int starRow,int endRow,int endWith,HSSFSheet hssfSheet){
        CellStyle cellStyle = hssfSheet.getWorkbook().createCellStyle();
//        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
//        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
//
//        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
//        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
//        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
         cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        for (int i = starRow; i <= endRow; i++) {
            HSSFRow row = hssfSheet.getRow(i);
            if (row == null){
                continue;
            }
            Iterator<Cell> cellIterator = row.cellIterator();
            int startColumIndex = 0;
            while (cellIterator.hasNext()) {
                Cell next = cellIterator.next();
                int columnIndex = next.getColumnIndex();

                for (int j = startColumIndex; j < columnIndex; j++) {
                    row.createCell(j).setCellStyle(cellStyle);
                }
                next.setCellStyle(cellStyle);

                if (!cellIterator.hasNext() && columnIndex < (endWith - 1)){
                    for (int j = columnIndex + 1; j < endWith; j++) {
                        row.createCell(j).setCellStyle(cellStyle);
                    }
                }
                startColumIndex = columnIndex + 1;
            }


        }

    }

    /**
     * @param reviewItemSecondDto:
     * @Description:生成详细评审个人的excel
     * @Author: zhongdl
     * @Date:2022/5/13 16:29
     * @return: {@link java.lang.String }
     **/
    public static String detailedPersonalExcel(ReviewItemSecondDto reviewItemSecondDto) {
        ByteArrayOutputStream bos = null;
        InputStream in = null;
        PersistenceService persistenceService = SpringUtil.getBean(PersistenceService.class);
        try (HSSFWorkbook workbook = new HSSFWorkbook();
        ) {
            String sheetName = "详细评审-" + reviewItemSecondDto.getSecondReviewItemName();
            //生成一个表格，设置表格名称
            HSSFSheet sheet = workbook.createSheet(reviewItemSecondDto.getSecondReviewItemName());

            List<ReviewItemThirdDto> reviewItemThirdDtos = reviewItemSecondDto.getReviewItemThirdDtos();
            //总宽度
            int totalWidth = reviewItemThirdDtos.size() + 3;
            //结束列
            int endWidth = Math.max(totalWidth, 22);
            //第一行
            HSSFRow row1 = sheet.createRow(0);
            row1.createCell(0).setCellValue(sheetName);

            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            // 行和列都是从0开始计数，且起始结束都会合并
            // 这里是合并excel中日期的两行为一行
            createCellRangeAddress(sheet,0, 0, 0, (endWidth - 1));
            int average = endWidth / 2;
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 16);  //字体大小
            font.setFontName("宋体");
            //第二行
            HSSFRow row2 = sheet.createRow(1);
            row2.createCell(0).setCellValue("招标项目名称:" + reviewItemSecondDto.getProjectName());
            HSSFCellStyle cellStyle1 = row2.getCell(0).getCellStyle();
            cellStyle1.setFont(font);
            cellStyle1.setAlignment(HorizontalAlignment.CENTER); // 居中
            createCellRangeAddress(sheet,1, 1, 0,  average - 1 );

            row2.createCell(average).setCellValue("招标项目编号:" + reviewItemSecondDto.getProjectCode());
            createCellRangeAddress(sheet,1, 1, average,  (endWidth - 1));
            //第三行
            HSSFRow row3 = sheet.createRow(2);
            row3.createCell(0).setCellValue("标段(包)名称:" + reviewItemSecondDto.getSectionName());
            createCellRangeAddress(sheet,2, 2, 0,  average - 1);


            row3.createCell(average).setCellValue("标段(包)编号:" + reviewItemSecondDto.getSectionCode());
            createCellRangeAddress(sheet,2, 2, average,  (endWidth - 1) );

            //第四行
            HSSFRow row4 = sheet.createRow(3);
            //第一列
            row4.createCell(0).setCellValue("序号");
            createCellRangeAddress(sheet,3, 4, 0,  0 );

            //第二列
            row4.createCell(1).setCellValue("投标人");
            createCellRangeAddress(sheet,3, 4, 1,  1);

            //第三列
            row4.createCell(2).setCellValue("评审因素");
            createCellRangeAddress(sheet,3, 3, 2,   (endWidth - 1));

            //第五行
            HSSFRow row5 = sheet.createRow(4);
            int reviewItemThirdSize = reviewItemThirdDtos.size();
            int reviewItemThirdFirstRow = 2;
            int reviewItemThirdColumnWith = (endWidth - 2) / (reviewItemThirdSize + 1);
            for (int i = 0; i < reviewItemThirdSize ; i++) {
                ReviewItemThirdDto reviewItemThirdDto = reviewItemThirdDtos.get(i);
                row5.createCell(reviewItemThirdFirstRow).setCellValue(reviewItemThirdDto.getThirdReviewItemName());
                int cellEndColunm = reviewItemThirdFirstRow + reviewItemThirdColumnWith - 1;
                if (cellEndColunm > reviewItemThirdFirstRow){
                    createCellRangeAddress(sheet,4, 4, reviewItemThirdFirstRow,cellEndColunm);
                }

                reviewItemThirdFirstRow += (reviewItemThirdColumnWith);
            }
            row5.createCell(reviewItemThirdFirstRow).setCellValue("最终得分");
            createCellRangeAddress(sheet,4, 4, reviewItemThirdFirstRow, (endWidth - 1));

            //开始行第5行
            int orgFirstRow = 5;
            List<EvaluationSecond> orgIdList = CollUtil.list(false);
            if (CollUtil.isNotEmpty(reviewItemSecondDto.getOrgIdList())){
                orgIdList = reviewItemSecondDto.getOrgIdList();
            }
            int orgSize = orgIdList.size();
            //评审点
            List<EvaluationThirdDTO> evaluationThirdList = CollUtil.isEmpty(reviewItemSecondDto.getEvaluationThirdDTOS()) ? CollUtil.list(false): reviewItemSecondDto.getEvaluationThirdDTOS();
            for (int i = 0; i < orgSize; i++) {
                EvaluationSecond evaluationSecond = orgIdList.get(i);
                HSSFRow row = sheet.createRow(orgFirstRow);
                row.createCell(0).setCellValue(i + 1);
                row.createCell(1).setCellValue(evaluationSecond.getOrgName());
                //初级评审只有定性
                reviewItemThirdFirstRow = 2;
                double sumScore = 0;
                List<Integer> cellAddressesIndex = CollUtil.list(false);
                for (ReviewItemThirdDto reviewItemThirdDto : reviewItemThirdDtos) {
                    //获得当前公司的当前评审点
                    List<String> approveFlag = evaluationThirdList.stream().filter(evaluationThird -> evaluationThird.getOrgId() != null && evaluationThird.getOrgId().equals(evaluationSecond.getOrgId()) && evaluationThird.getExpertId()
                            .equals(reviewItemSecondDto.getExpertId()) && evaluationThird.getThirdReviewItemId().equals(reviewItemThirdDto.getId())).map(EvaluationThirdDTO::getFinalScore).collect(Collectors.toList());
                    Double averageValue = getAverageValue(approveFlag);
                    row.createCell(reviewItemThirdFirstRow).setCellValue(formatScore(averageValue));
                    int cellEndColunm = reviewItemThirdFirstRow + reviewItemThirdColumnWith - 1;

                    if (cellEndColunm > reviewItemThirdFirstRow) {

                        createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, reviewItemThirdFirstRow, cellEndColunm);

                    }
                    reviewItemThirdFirstRow += (reviewItemThirdColumnWith);
                    cellAddressesIndex.add(sheet.getMergedRegions().size() - 1);
                    sumScore += averageValue;
                }
                if (sumScore == 0){
                    sheet.removeMergedRegions(cellAddressesIndex);
                    sheet.removeRow(row);
                    continue;
                }
                sumScore = twoDecimal(sumScore);
                row.createCell(reviewItemThirdFirstRow).setCellValue(formatScore(sumScore));
                createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, reviewItemThirdFirstRow, (endWidth - 1 ));

                orgFirstRow++;
            }
            //设置备注签子
            HSSFRow row6 = sheet.createRow(orgFirstRow);
            row6.createCell(0).setCellValue("备注:");
             createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, 0,   (endWidth - 1) );
            orgFirstRow++;
            //设置专家签子
            HSSFRow row7 = sheet.createRow(orgFirstRow);
            HSSFCell cell = row7.createCell(0);

            row7.createCell(0).setCellValue("专家签字：" + reviewItemSecondDto.getExpertName());
            createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, 0,   (endWidth - 1) );
            orgFirstRow++;
            //设置注释
            HSSFRow row8 = sheet.createRow(orgFirstRow);
            row8.createCell(0).setCellValue("日期:" + DateUtil.format(new Date(),new SimpleDateFormat("yyyy-MM-dd")));
            createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, 0,   (endWidth - 1) );
            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] brray = bos.toByteArray();
            in = new ByteArrayInputStream(brray);
            UploadEntity uploadEntity = new UploadEntity();
            String url = persistenceService.updateToOss(uploadEntity, in, sheetName + ".xlsx");
            in.close();
            bos.close();
            log.info("生成的url{}" + url);
            return url;

        } catch (IOException e) {
            e.printStackTrace();


        }finally {
            if (bos != null){
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (in != null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }



    /**
     * 将文件转换成byte数组
     * @param filePath  文件File类 通过new File(文件路径)
     * @return byte数组
     */
    public static byte[] getFile2byte(File filePath) {
        byte[] buffer = null;
        try {
            FileInputStream fis = new FileInputStream(filePath);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            byte[] b = new byte[1024];
            int n;
            while ((n = fis.read(b)) != -1) {
                bos.write(b, 0, n);
            }
            fis.close();
            bos.close();
            buffer = bos.toByteArray();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return buffer;
    }

    /**
     * @Description: 将base64编码字符串转换为图片
     * @Author:
     * @CreateTime:
     * @param file base64编码字符串
     * @return
     */
    public static String generateImage(String file) {
        // 解密
        try {
            // 项目绝对路径
            String savePath = new File("temps").getAbsolutePath();
            // 图片分类路径+图片名+图片后缀
            // 解密
            Base64.Decoder decoder = Base64.getDecoder();
            // 去掉base64前缀 data:image/jpeg;base64,
            file = file.substring(file.indexOf(",", 1) + 1, file.length());
            byte[] b = decoder.decode(file);
            // 处理数据
            for (int i = 0; i < b.length; ++i) {
                if (b[i] < 0) {
                    b[i] += 256;
                }
            }
            String imagePath = savePath + File.separator + UUID.randomUUID().toString() + ".png";
            // 保存图片
            OutputStream out = new FileOutputStream(imagePath);
            out.write(b);
            out.flush();
            out.close();
            // 返回图片的相对路径 = 图片分类路径+图片名+图片后缀
            return imagePath;
        } catch (IOException e) {
            return null;
        }
    }




    public static String detailedSummary(ReviewItemSecondDto reviewItemSecondDto,List<EvaluationCommitteeMembers> membersList) {
        ByteArrayOutputStream bos = null;
        InputStream in = null;
        PersistenceService persistenceService = SpringUtil.getBean(PersistenceService.class);
        try (HSSFWorkbook workbook = new HSSFWorkbook();
        ) {
            String sheetName = "详细评审-" + reviewItemSecondDto.getSecondReviewItemName() + "汇总表";

            int expertRowNum = 1;
            if (CollUtil.isNotEmpty(membersList)){
                expertRowNum = (int) Math.ceil(membersList.size() / 7.0);
            }


            //生成一个表格，设置表格名称
            HSSFSheet sheet = workbook.createSheet(reviewItemSecondDto.getSecondReviewItemName());

            List<ReviewItemThirdDto> reviewItemThirdDtos = reviewItemSecondDto.getReviewItemThirdDtos();
            //总宽度
            int totalWidth = reviewItemThirdDtos.size() + 3;
            //结束列
            int endWidth = Math.max(totalWidth, 22);
            //第一行
            HSSFRow row1 = sheet.createRow(0);

            row1.createCell(0).setCellValue(sheetName);
            //第一列
            sheet.setColumnWidth(0, (int) ((3.74 + 0.72) * 256));


            //第二列
            sheet.setColumnWidth(1, (int) ((22.62 + 0.72) * 256));

            row1.setHeight((short) (39.95 *20));
            int columnwith = (int)(4.62 * 256 + 184);
            for (int i = 2; i < endWidth; i++) {
                sheet.setColumnWidth(i, columnwith);
            }
            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            // 行和列都是从0开始计数，且起始结束都会合并
            // 这里是合并excel中日期的两行为一行
            createCellRangeAddress(sheet,0, 0, 0, (endWidth - 1) );

            int average = endWidth / 2;
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 16);  //字体大小
            font.setFontName("宋体");
            //第二行
            HSSFRow row2 = sheet.createRow(1);
            row2.createCell(0).setCellValue("招标项目名称:" + reviewItemSecondDto.getProjectName());
            HSSFCellStyle cellStyle1 = row2.getCell(0).getCellStyle();
            cellStyle1.setFont(font);
            cellStyle1.setAlignment(HorizontalAlignment.CENTER); // 居中
            createCellRangeAddress(sheet,1, 1, 0,  average );
            row2.createCell(average + 1).setCellValue("招标项目编号:" + reviewItemSecondDto.getProjectCode());
            createCellRangeAddress(sheet,1, 1, average + 1,  (endWidth - 1) );

            //第三行
            HSSFRow row3 = sheet.createRow(2);
            row3.createCell(0).setCellValue("标段(包)名称:" + reviewItemSecondDto.getSectionName());
             createCellRangeAddress(sheet,2, 2, 0,  average );


            row3.createCell(average + 1).setCellValue("标段(包)编号:" + reviewItemSecondDto.getSectionCode());
            createCellRangeAddress(sheet,2, 2, average + 1,  (endWidth - 1) );

            //第四行
            HSSFRow row4 = sheet.createRow(3);
            //第一列
            row4.createCell(0).setCellValue("序号");
             createCellRangeAddress(sheet,3, 4, 0,  0 );
            //第二列
            row4.createCell(1).setCellValue("投标人");
             createCellRangeAddress(sheet,3, 4, 1,  1 );
            //第三列
            row4.createCell(2).setCellValue("专家");
             createCellRangeAddress(sheet,3, 3, 2,   (endWidth - 1) );
            //第五行
            HSSFRow row5 = sheet.createRow(4);
            int reviewItemThirdSize = membersList.size();
            int reviewItemThirdFirstRow = 2;
            int reviewItemThirdColumnWith = (endWidth - 2) / (reviewItemThirdSize + 1);
            //TODO 计算平均分
            for (int i = 0; i < reviewItemThirdSize ; i++) {
                EvaluationCommitteeMembers reviewItemThirdDto = membersList.get(i);
                row5.createCell(reviewItemThirdFirstRow).setCellValue(reviewItemThirdDto.getMemberName());
                int cellendColum = reviewItemThirdFirstRow + reviewItemThirdColumnWith - 1;
                if (cellendColum > reviewItemThirdFirstRow){
                    createCellRangeAddress(sheet,4, 4, reviewItemThirdFirstRow, cellendColum );
                }


                reviewItemThirdFirstRow += (reviewItemThirdColumnWith);
            }
            row5.createCell(reviewItemThirdFirstRow).setCellValue("最终得分");
            createCellRangeAddress(sheet,4, 4, reviewItemThirdFirstRow, (endWidth - 1) );
            //开始行第5行
            int orgFirstRow = 5;
            List<EvaluationSecond> orgIdList = CollUtil.list(false);
            if (CollUtil.isNotEmpty(reviewItemSecondDto.getOrgIdList())){
                orgIdList = reviewItemSecondDto.getOrgIdList();
            }
            int orgSize = orgIdList.size();
            //评审点
            List<EvaluationSecond> evaluationSecondList = CollUtil.isEmpty(reviewItemSecondDto.getEvaluationSeconds()) ? CollUtil.list(false) : reviewItemSecondDto.getEvaluationSeconds();
            List<EvaluationThirdDTO> evaluationThirdList = CollUtil.isEmpty(reviewItemSecondDto.getEvaluationThirdDTOS()) ? CollUtil.list(false): reviewItemSecondDto.getEvaluationThirdDTOS();

            for (int i = 0; i < orgSize; i++) {
                EvaluationSecond evaluationSecond = orgIdList.get(i);
                HSSFRow row = sheet.createRow(orgFirstRow);
                row.createCell(0).setCellValue(i + 1);
                row.createCell(1).setCellValue(evaluationSecond.getOrgName());
                //初级评审只有定性
                reviewItemThirdFirstRow = 2;
                Double sumScore = 0.0;
                List<Integer> cellAddressesIndex = CollUtil.list(false);

                for (int j = 0; j < reviewItemThirdSize; j++) {
                    EvaluationCommitteeMembers evaluationCommitteeMembers = membersList.get(j);
                    List<Double> finalScore = evaluationSecondList.stream().filter(second -> evaluationCommitteeMembers.getMemberName().equals(second.getExpertName()) && evaluationSecond.getOrgId().equals(second.getOrgId()))
                            .map(EvaluationSecond::getFinalScore).collect(Collectors.toList());
                    Double averageValue = getAverageDoubleValue(finalScore);
                    sumScore += averageValue;
                    row.createCell(reviewItemThirdFirstRow).setCellValue(formatScore(averageValue));

                    int cellendColum = reviewItemThirdFirstRow + reviewItemThirdColumnWith - 1;
                    if (cellendColum > reviewItemThirdFirstRow){
                        createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, reviewItemThirdFirstRow, cellendColum );
                    }
                    cellAddressesIndex.add(sheet.getMergedRegions().size() - 1);
                    reviewItemThirdFirstRow += (reviewItemThirdColumnWith);
                }
                if (sumScore == 0.0){
                    sheet.removeMergedRegions(cellAddressesIndex);
                    sheet.removeRow(row);
                    continue;
                }
                //计算总平均分
                //获得当前公司的当前评审点
                List<EvaluationThirdDTO> approveList = evaluationThirdList.stream().filter(evaluationThird -> evaluationSecond.getOrgId().equals(evaluationThird.getOrgId()))
                        .collect(Collectors.toList());
                List<String> finalScoreList = approveList.stream().map(EvaluationThirdDTO::getFinalScore).collect(Collectors.toList());
                int expertCount = approveList.stream().map(EvaluationThirdDTO::getExpertId).collect(Collectors.toSet()).size();
                Double averageValue = getAverageValue(finalScoreList,expertCount);
                row.createCell(reviewItemThirdFirstRow).setCellValue(formatScore(averageValue));
                 createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, reviewItemThirdFirstRow, (endWidth - 1 ));
                orgFirstRow++;
            }
            //设置备注签子
            HSSFRow row6 = sheet.createRow(orgFirstRow);
            row6.createCell(0).setCellValue("备注:");
             createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, 0,   (endWidth - 1) );
            orgFirstRow++;
            //设置专家签子
            HSSFRow row7 = sheet.createRow(orgFirstRow);
            row7.createCell(0).setCellValue("专家签字：");
            createCellRangeAddress(sheet,orgFirstRow, expertRowNum > 1 ? (orgFirstRow + expertRowNum) : orgFirstRow, 0,   (endWidth - 1) );

            if (expertRowNum > 1){
                orgFirstRow += expertRowNum;
            }
            //设置边框
            setFrame(3,orgFirstRow,endWidth,sheet);
            orgFirstRow++;
            //设置注释
            HSSFRow row8 = sheet.createRow(orgFirstRow);
            row8.createCell(0).setCellValue("日期:" + DateUtil.format(new Date(),new SimpleDateFormat("yyyy-MM-dd")));
             createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, 0,  (endWidth - 1) );

            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] brray = bos.toByteArray();
            in = new ByteArrayInputStream(brray);
            UploadEntity uploadEntity = new UploadEntity();
            String url = persistenceService.updateToOss(uploadEntity, in, sheetName + ".xlsx");
            in.close();
            bos.close();
            log.info("生成的url{}" + url);
            return url;

        } catch (IOException e) {
            e.printStackTrace();


        }finally {
            if (bos != null){
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (in != null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
    /**
     * @param strValue:
     * @Description:计算平均分
     * @Author: zhongdl
     * @Date:2022/5/25 14:25

     * @return: {@link java.lang.Double }
     **/
    public static Double getAverageValue(List<String> strValue){
        List<Double> list = CollUtil.list(false);
        if (CollUtil.isEmpty(strValue)){
            return 0.0;
        }
        for (String str : strValue) {
            if (StrUtil.isEmpty(str)){
                list.add(0.0);
                continue;
            }
            list.add(Double.parseDouble(str));
        }
        Double value = list.stream().collect(Collectors.averagingDouble(Double::doubleValue));
        BigDecimal two = new BigDecimal(value);
        return two.setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
    }
    /**
     * @param strValue:
     * @param expertCount:专家数量
     * @Description:计算平均分
     * @Author: zhongdl
     * @Date:2022/5/25 14:25

     * @return: {@link java.lang.Double }
     **/
    public static Double getAverageValue(List<String> strValue,Integer expertCount){
        List<Double> list = CollUtil.list(false);
        if (CollUtil.isEmpty(strValue)){
            return 0.0;
        }
        if (expertCount == null){
            expertCount = 1;
        }
        for (String str : strValue) {
            if (StrUtil.isEmpty(str)){
                list.add(0.0);
                continue;
            }
            list.add(Double.parseDouble(str));
        }
        Double value = list.stream().reduce(0.0, (a, b) -> a + b);
        BigDecimal two = new BigDecimal(value / expertCount);
        return two.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
    }

    public static Double getAverageDoubleValue(List<Double> strValue){
        List<Double> doubleList = CollUtil.list(false);
        for (Double aDouble : strValue) {
             if (aDouble == null){
                 doubleList.add(0.0);
                 continue;
             }
            doubleList.add(aDouble);
        }

        Double value = doubleList.stream().collect(Collectors.averagingDouble(Double::doubleValue));
        BigDecimal two = new BigDecimal(value);
        return two.setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
    }
    /**
     * @param bigDecimal: 数值
     * @param format:#0.00 两位小数 #0.000 三位小数
     * @Description:将数值类型默认更新为2位小数
     * @Author: zhongdl
     * @Date:2022/5/25 16:33

     * @return: {@link java.lang.String }
     **/
    public static String formatScore(BigDecimal bigDecimal,String format){
        if (StrUtil.isEmpty(format)){
            format = "#0.00";
        }
        DecimalFormat decimalFormat = new DecimalFormat("#0.00");
        String bigDecimalStr = decimalFormat.format(bigDecimal);
        return bigDecimalStr;
    }
    /**
     * @param score: 根虎
     * @Description:将数值类型默认更新为2位小数
     * @Author: zhongdl
     * @Date:2022/5/25 16:33

     * @return: {@link java.lang.String }
     **/
    public static String formatScore(Double score){
        if (score == null){
            score = 0.0;
        }
        return formatScore(new BigDecimal(score),null);
    }

    public static Double twoDecimal (Double doube){
        if (doube == null){
            return 0.00;
        }

        BigDecimal two = new BigDecimal(doube);
        return two.setScale(2, RoundingMode.HALF_UP).doubleValue();
    }
    /**
     * @param data:
     * @param bidSectionDTO:
     * @Description:生成报价评审excel
     * @Author: zhongdl
     * @Date:2022/5/13 17:26

     * @return: {@link java.lang.String }
     **/
    public static String quotationReviewExcel(List<QuotationReviewExcelDTO> data, BidSectionDTO bidSectionDTO) {
        ByteArrayOutputStream bos = null;
        InputStream in = null;
        PersistenceService persistenceService = SpringUtil.getBean(PersistenceService.class);
        try (HSSFWorkbook workbook = new HSSFWorkbook();
        ) {
            String sheetName = "报价评审";
            int expertRowNum = 1;
            List<EvaluationCommitteeMembers> membersList = CollUtil.list(false);
            if(CollUtil.isNotEmpty(data) && CollUtil.isNotEmpty(data.get(0).getMembersList())){
                membersList = data.get(0).getMembersList();
            }

            if (CollUtil.isNotEmpty(membersList)){
                expertRowNum = (int) Math.ceil(membersList.size() / 3.0);
            }

            InputStream stream = SpringUtils.getLocalStream("template/pingbiaobaogaomoban.xlsx");
            XSSFWorkbook templateWorkbook = new XSSFWorkbook(stream);
            XSSFSheet sheetAt = templateWorkbook.getSheetAt(3);

            //生成一个表格，设置表格名称
            HSSFSheet sheet = workbook.createSheet(sheetName);
            //结束列
            int endWidth = 12;
            //第一行
            HSSFRow row1 = sheet.createRow(0);
            row1.createCell(0).setCellValue(sheetName);
            //第一行

            row1.createCell(0).setCellValue(sheetName);
            for (int i = 0; i < 22; i++) {
                sheet.setColumnWidth(i,sheetAt.getColumnWidth(i));
            }


            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            // 行和列都是从0开始计数，且起始结束都会合并
            // 这里是合并excel中日期的两行为一行
             createCellRangeAddress(sheet,0, 0, 0, (endWidth - 1) );
            int average = endWidth / 2;
            boolean flag = average > 2;
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 16);  //字体大小
            font.setFontName("宋体");
            //第二行
            HSSFRow row2 = sheet.createRow(1);
            row2.createCell(0).setCellValue("招标项目名称:" + bidSectionDTO.getProjectName());
            HSSFCellStyle cellStyle1 = row2.getCell(0).getCellStyle();
            cellStyle1.setFont(font);
            cellStyle1.setAlignment(HorizontalAlignment.CENTER); // 居中
             createCellRangeAddress(sheet,1, 1, 0,  average );

            row2.createCell(average + 1).setCellValue("招标项目编号:" + bidSectionDTO.getProjectCode());
            if (flag){
                 createCellRangeAddress(sheet,1, 1, average + 1,  (endWidth - 1) );
            }
            //第三行
            HSSFRow row3 = sheet.createRow(2);
            row3.createCell(0).setCellValue("标段(包)名称:" + bidSectionDTO.getSectionName());
            createCellRangeAddress(sheet,2, 2, 0,  average );


            row3.createCell(average + 1).setCellValue("标段(包)编号:" + bidSectionDTO.getSectionCode());
            if (flag){
                createCellRangeAddress(sheet,2, 2, average + 1,  (endWidth - 1) );
            }
            //第四行
            HSSFRow row4 = sheet.createRow(3);

            //第一列
            row4.createCell(0).setCellValue("序号");
            //第二列
            row4.createCell(1).setCellValue("投标单位名称");
            createCellRangeAddress(sheet,3, 3, 1,  3 );
            //第三列
            row4.createCell(4).setCellValue("评标基准价(元)");
            createCellRangeAddress(sheet,3, 3, 4,   7 );
            //第四列
            row4.createCell(8).setCellValue("投标报价(元)");
            //第五列
            row4.createCell(9).setCellValue("小微修正价(元)");
            //第六列
            row4.createCell(10).setCellValue("得分");
            //第六列
            row4.createCell(11).setCellValue("排名");
            if (CollUtil.isEmpty(data)){
                data = CollUtil.list(false);
            }
            //排序分数
            List<QuotationReviewExcelDTO> quotationReviewList = data.stream().sorted(Comparator.comparing(QuotationReviewExcelDTO::getFinalScore).reversed()).collect(Collectors.toList());
            //开始行
            int orgFirstRow = 4;
            int size = quotationReviewList.size();
            for (int i = 0; i < size; i++) {
                QuotationReviewExcelDTO quotationReview = quotationReviewList.get(i);
                HSSFRow row = sheet.createRow(orgFirstRow);
                //序号
                row.createCell(0).setCellValue(i + 1);
                //投标单位名称
                row.createCell(1).setCellValue(quotationReview.getOrgName());
                createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, 1,  3 );
                //评标基准价(元)
                row.createCell(4).setCellValue("0");
                 createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, 4,   7 );
                //投标报价(元)
                row.createCell(8).setCellValue(quotationReview.getQuotedPrice());
                //小微修正价(元)
                row.createCell(9).setCellValue("0");
                //得分
                row.createCell(10).setCellValue(formatScore(quotationReview.getFinalScore()));
                //排名
                row.createCell(11).setCellValue(i + 1);
                orgFirstRow++;
            }
            setFrame(3,orgFirstRow-1,endWidth,sheet);
            //设置专家签子
            HSSFRow row7 = sheet.createRow(orgFirstRow);
            row7.createCell(0).setCellValue("专家签字：");
           createCellRangeAddress(sheet,orgFirstRow, expertRowNum > 1 ? (expertRowNum + orgFirstRow) : orgFirstRow, 0,   (endWidth - 1) );
           if (expertRowNum > 1){
               orgFirstRow += expertRowNum;
           }
            orgFirstRow++;
            //设置注释
            HSSFRow row8 = sheet.createRow(orgFirstRow);

            row8.createCell(0).setCellValue("日期:" + DateUtil.format(new Date(),new SimpleDateFormat("yyyy-MM-dd")));
            createCellRangeAddress(sheet,orgFirstRow, orgFirstRow, 0,  (endWidth - 1) );
            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] brray = bos.toByteArray();
            in = new ByteArrayInputStream(brray);
            UploadEntity uploadEntity = new UploadEntity();
            String url = persistenceService.updateToOss(uploadEntity, in, sheetName + ".xlsx");
            in.close();
            bos.close();
            stream.close();
            log.info("生成的url{}" + url);
            return url;

        } catch (IOException e) {
            e.printStackTrace();


        }finally {
            if (bos != null){
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (in != null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
    /**
     *
     * @Description:生成评分汇总
     * @Author: zhongdl
     * @Date:2022/4/21 13:43
     * @param scoreSummaryExcelDTO:
     * @return: {@link String }
     **/
    public static String scoreSummary(ScoreSummaryExcelDTO scoreSummaryExcelDTO,List<EvaluationCommitteeMembers> membersList) {
        List<ReviewItemSecondDto> reviewItemSecondList = scoreSummaryExcelDTO.getReviewItemSecondList();
        List<ScoreSummarySecondDTO> orgList = scoreSummaryExcelDTO.getOrgList();
        if (CollUtil.isEmpty(reviewItemSecondList)){
            return null;
        }
        ByteArrayOutputStream bos = null;
        InputStream in = null;
        PersistenceService persistenceService = SpringUtil.getBean(PersistenceService.class);
        try (HSSFWorkbook workbook = new HSSFWorkbook();
        ) {
            InputStream stream = SpringUtils.getLocalStream("template/pingbiaobaogaomoban.xlsx");
            XSSFWorkbook templateWorkbook = new XSSFWorkbook(stream);
            XSSFSheet sheetAt = templateWorkbook.getSheetAt(4);

            String sheetName = "评分汇总表";
            int expertRowNum = 1;
            if (CollUtil.isNotEmpty(membersList)){
                expertRowNum = (int) Math.ceil(membersList.size() / 7.0);
            }


            //生成一个表格，设置表格名称
            HSSFSheet sheet = workbook.createSheet(sheetName);
            for (int i = 0; i < 22; i++) {
                sheet.setColumnWidth(i, sheetAt.getColumnWidth(i));
            }
            //总宽度
            int totalWidth = reviewItemSecondList.size() + 3;
            //结束列
            int endWidth = Math.max(totalWidth, 22);
            //第一行
            HSSFRow row1 = sheet.createRow(0);
            row1.createCell(0).setCellValue(sheetName);



            row1.setHeight((short) (39.95 *20));

            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            // 行和列都是从0开始计数，且起始结束都会合并
            // 这里是合并excel中日期的两行为一行
             createCellRangeAddress(sheet,0, 0, 0, (endWidth - 1) );
            int average = endWidth / 2;
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 16);  //字体大小
            font.setFontName("宋体");
            //第二行
            HSSFRow row2 = sheet.createRow(1);
            row2.createCell(0).setCellValue("招标项目名称:" + scoreSummaryExcelDTO.getProjectName());
            HSSFCellStyle cellStyle1 = row2.getCell(0).getCellStyle();
            cellStyle1.setFont(font);
            cellStyle1.setAlignment(HorizontalAlignment.CENTER); // 居中
            createCellRangeAddress(sheet,1, 1, 0,  average );
            row2.createCell(average + 1).setCellValue("招标项目编号:" + scoreSummaryExcelDTO.getProjectCode());
            createCellRangeAddress(sheet,1, 1, average + 1,  (endWidth - 1) );

            //第三行
            HSSFRow row3 = sheet.createRow(2);
            row3.createCell(0).setCellValue("标段(包)名称:" + scoreSummaryExcelDTO.getSectionName());
            createCellRangeAddress(sheet,2, 2, 0,  average );


            row3.createCell(average + 1).setCellValue("标段(包)编号:" + scoreSummaryExcelDTO.getSectionCode());
             createCellRangeAddress(sheet,2, 2, average + 1,  (endWidth - 1) );

            //序号	投标人	商务部分及技术部分	评标价算法	合计得分

            //第四行
            HSSFRow row4 = sheet.createRow(3);
            //第一列
            row4.createCell(0).setCellValue("序号");

            //第二列
            row4.createCell(1).setCellValue("投标人");
            int size = reviewItemSecondList.size();
            int firstColumn = 2;
            //计算每列站多少格子
            int eachColumn = (endWidth - 2) /(size + 2);
            for (ReviewItemSecondDto reviewItemSecondDto : reviewItemSecondList) {
                row4.createCell(firstColumn).setCellValue(reviewItemSecondDto.getSecondReviewItemName());
                createCellRangeAddress(sheet, 3, 3, firstColumn, (firstColumn + eachColumn - 1));
                firstColumn += eachColumn;
            }
            row4.createCell(firstColumn).setCellValue("合计得分");
            createCellRangeAddress(sheet,3, 3, firstColumn, (firstColumn + eachColumn - 1) );
            firstColumn += eachColumn;
            row4.createCell(firstColumn).setCellValue("排名");
            createCellRangeAddress(sheet,3, 3, firstColumn,  (endWidth - 1) );

            //设置分数
            int orgSize = orgList.size();
            //开始行
            int startRow = 4;

            for (int i = 0; i < orgSize; i++) {
                ScoreSummarySecondDTO scoreSummarySecondDTO = orgList.get(i);

                List<ReviewItemSecondDto> reviewItemSecondDtos = scoreSummarySecondDTO.getReviewItemSecondDtos();
                int pointSize = Math.min(reviewItemSecondDtos.size(), size);
                double sumScore = 0.0;
                for (int j = 0; j < pointSize; j++) {
                    ReviewItemSecondDto reviewItemSecondDto = reviewItemSecondDtos.get(j);
                    sumScore += reviewItemSecondDto.getFinalScore();
                }
                scoreSummarySecondDTO.setSumScore(sumScore);
            }
            List<ScoreSummarySecondDTO> collect = orgList.stream().sorted(Comparator.comparing(ScoreSummarySecondDTO::getSumScore).reversed()).collect(Collectors.toList());
            for (int i = 0; i < orgSize; i++) {
                HSSFRow row = sheet.createRow(startRow);
                ScoreSummarySecondDTO scoreSummarySecondDTO = collect.get(i);
                row.createCell(0).setCellValue(i + 1);
                row.createCell(1).setCellValue(scoreSummarySecondDTO.getOrgName());
                List<ReviewItemSecondDto> reviewItemSecondDtos = scoreSummarySecondDTO.getReviewItemSecondDtos();
                int pointSize = Math.min(reviewItemSecondDtos.size(), size);
                firstColumn = 2;
                double sumScore = 0.0;
                for (int j = 0; j < pointSize; j++) {
                    ReviewItemSecondDto reviewItemSecondDto = reviewItemSecondDtos.get(j);
                    row.createCell(firstColumn).setCellValue(formatScore(reviewItemSecondDto.getFinalScore()));
                    sumScore += reviewItemSecondDto.getFinalScore();
                     createCellRangeAddress(sheet,startRow, startRow, firstColumn,  (firstColumn + eachColumn -1) );
                    firstColumn += eachColumn;
                }
                //设置合计得分
                scoreSummarySecondDTO.setSumScore(sumScore);
                sumScore = twoDecimal(sumScore);
                row.createCell(firstColumn).setCellValue(formatScore(sumScore));
                 createCellRangeAddress(sheet,startRow, startRow, firstColumn,  (firstColumn + eachColumn -1) );
                firstColumn += eachColumn;
                //设置排名
                row.createCell(firstColumn).setCellValue(i + 1);
                createCellRangeAddress(sheet,startRow, startRow, firstColumn,  (endWidth -1) );
                startRow ++;
            }
            setFrame(3,startRow - 1 ,endWidth,sheet);

            //设置注释
            HSSFRow row8 = sheet.createRow(startRow);
            row8.createCell(0).setCellValue("专家签字:");
             createCellRangeAddress(sheet,startRow, expertRowNum > 1 ? (startRow + expertRowNum) : startRow, 0,  (endWidth - 1) );
             if (expertRowNum > 1){
                 startRow += expertRowNum;
             }
            //设置边框
            startRow++;
            HSSFRow row9 = sheet.createRow(startRow);
            row9.createCell(0).setCellValue("日期:" + DateUtil.format(new Date(),new SimpleDateFormat("yyyy-MM-dd")));
             createCellRangeAddress(sheet,startRow, startRow, 0,  (endWidth - 1) );

            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] brray = bos.toByteArray();
            in = new ByteArrayInputStream(brray);
            UploadEntity uploadEntity = new UploadEntity();
            String url = persistenceService.updateToOss(uploadEntity, in, sheetName + ".xlsx");
            in.close();
            bos.close();
            stream.close();
            log.info("生成的生成评分汇总url: {}" + url);
            return url;

        } catch (IOException e) {
            e.printStackTrace();


        }finally {
            if (bos != null){
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (in != null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static String scoreConclusion(ScoreSummaryExcelDTO scoreSummaryExcelDTO,String tendereeName,List<EvaluationCommitteeMembers> membersList) {
        List<ReviewItemSecondDto> reviewItemSecondList = scoreSummaryExcelDTO.getReviewItemSecondList();
        List<ScoreSummarySecondDTO> orgList = scoreSummaryExcelDTO.getOrgList();
        if (CollUtil.isEmpty(reviewItemSecondList)){
            return null;
        }
        ByteArrayOutputStream bos = null;
        InputStream in = null;
        PersistenceService persistenceService = SpringUtil.getBean(PersistenceService.class);
        try (HSSFWorkbook workbook = new HSSFWorkbook();
        ) {
            String sheetName = "评标结论表";
            InputStream stream = SpringUtils.getLocalStream("template/pingbiaobaogaomoban.xlsx");

            XSSFWorkbook templateWorkbook = new XSSFWorkbook(stream);
            XSSFSheet sheetAt = templateWorkbook.getSheetAt(5);

            int expertRowNum = 1;
            if (CollUtil.isNotEmpty(membersList)){
                expertRowNum = (int) Math.ceil(membersList.size() / 7.0);
            }

            //生成一个表格，设置表格名称
            HSSFSheet sheet = workbook.createSheet(sheetName);

            //总宽度
            int totalWidth = reviewItemSecondList.size() + 3;
            //结束列
            int endWidth = 11;
            //第一行
            HSSFRow row1 = sheet.createRow(0);
            row1.createCell(0).setCellValue(sheetName);
            row1.setHeight((short) (3.83 *256));
            for (int i = 0; i < 22; i++) {
                sheet.setColumnWidth(i, sheetAt.getColumnWidth(i));
            }
            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            // 行和列都是从0开始计数，且起始结束都会合并
            // 这里是合并excel中日期的两行为一行
             createCellRangeAddress(sheet,0, 0, 0, (endWidth - 1) );
            int average = endWidth / 2;
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 16);  //字体大小
            font.setFontName("宋体");
            //第二行
            HSSFRow row2 = sheet.createRow(1);
            row2.createCell(0).setCellValue("招标项目名称:" + scoreSummaryExcelDTO.getProjectName());
            HSSFCellStyle cellStyle1 = row2.getCell(0).getCellStyle();
            cellStyle1.setFont(font);
            cellStyle1.setAlignment(HorizontalAlignment.CENTER); // 居中
             createCellRangeAddress(sheet,1, 1, 0,  5 );
            row2.createCell(6).setCellValue("招标项目编号:" + scoreSummaryExcelDTO.getProjectCode());
             createCellRangeAddress(sheet,1, 1, 6,  10 );

            //第三行
            HSSFRow row3 = sheet.createRow(2);
            row3.createCell(0).setCellValue("标段(包)名称:" + scoreSummaryExcelDTO.getSectionName());
             createCellRangeAddress(sheet,2, 2, 0,  5 );


            row3.createCell(6).setCellValue("标段(包)编号:" + scoreSummaryExcelDTO.getSectionCode());
            createCellRangeAddress(sheet,2, 2, 6,  10 );

            //序号	投标人	商务部分及技术部分	评标价算法	合计得分

            //第四行
            HSSFRow row4 = sheet.createRow(3);
            //第一列
            row4.createCell(0).setCellValue("招标人");


            //第二列
            row4.createCell(1).setCellValue(tendereeName);
             createCellRangeAddress(sheet,3, 3, 1,  (endWidth - 1) );

            //第五行
            HSSFRow row5 = sheet.createRow(4);
            row5.createCell(0).setCellValue("评审结果");
            createCellRangeAddress(sheet,4, 4, 0,  (endWidth - 1) );
            //第六行

            //投标人	投标报价(元)						小微修正价(元)	工期/交货/服务期	得分	名次
            HSSFRow row6 = sheet.createRow(5);
            row6.createCell(0).setCellValue("投标人");
            row6.createCell(1).setCellValue("投标报价(元)");
             createCellRangeAddress(sheet,5, 5, 1,  6 );
            row6.createCell(7).setCellValue("小微修正价(元)");
            row6.createCell(8).setCellValue("工期/交货/服务期");
            row6.createCell(9).setCellValue("得分");
            row6.createCell(10).setCellValue("名次");
            for (ScoreSummarySecondDTO scoreSummarySecondDTO : orgList) {
                List<ReviewItemSecondDto> reviewItemSecondDtos = scoreSummarySecondDTO.getReviewItemSecondDtos();
                if (CollUtil.isEmpty(reviewItemSecondDtos)){
                    scoreSummarySecondDTO.setFinalScore(0.0);
                    continue;
                }
                Double reduce = reviewItemSecondDtos.stream().map(ReviewItemSecondDto::getFinalScore).reduce(0.0, (a, b) -> a + b);
                scoreSummarySecondDTO.setFinalScore(reduce);
            }
            orgList = orgList.stream().sorted(Comparator.comparing(ScoreSummarySecondDTO::getFinalScore).reversed()).collect(Collectors.toList());
            int orgCount = Math.min(orgList.size(),3);
            int startRow = 6;
            for (int i = 0; i < orgCount; i++) {
                ScoreSummarySecondDTO scoreSummarySecondDTO = orgList.get(i);
                HSSFRow row = sheet.createRow(startRow);
                row.createCell(0).setCellValue(scoreSummarySecondDTO.getOrgName());
                row.createCell(1).setCellValue(scoreSummarySecondDTO.getQuotedPrice());
                 createCellRangeAddress(sheet,startRow, startRow, 1,  6 );
                row.createCell(7).setCellValue("0");
                row.createCell(8).setCellValue(scoreSummarySecondDTO.getPeriod());
                row.createCell(9).setCellValue(formatScore(scoreSummarySecondDTO.getFinalScore()));
                row.createCell(10).setCellValue(i + 1);
                startRow++;
            }
            //设置候选人列表
            HSSFRow row7 = sheet.createRow(startRow);
            row7.createCell(0).setCellValue("候选人");
            row7.createCell(1).setCellValue("中标候选人名称");
             createCellRangeAddress(sheet,startRow, startRow, 1,  10 );
            startRow++;
            for (int i = 0; i < orgCount; i++) {
                ScoreSummarySecondDTO scoreSummarySecondDTO = orgList.get(i);

                HSSFRow row = sheet.createRow(startRow);
                row.createCell(0).setCellValue("第"+(i + 1)+"候选人");
                row.createCell(1).setCellValue(scoreSummarySecondDTO.getOrgName());
                 createCellRangeAddress(sheet,startRow, startRow, 1,  10 );
                startRow++;
            }
            HSSFRow row8 = sheet.createRow(startRow);
            row8.createCell(0).setCellValue("评委意见");
            row8.createCell(1).setCellValue("");
            row8.setHeight((short) (79.95 * 20));
             createCellRangeAddress(sheet,startRow, startRow, 1,  10 );
            startRow++;

            HSSFRow row9 = sheet.createRow(startRow);
            row9.createCell(0).setCellValue("专家签字");
            row9.createCell(1).setCellValue("");
            row9.setHeight((short) (39.95 * 20));

             createCellRangeAddress(sheet,startRow, expertRowNum > 1 ? (startRow + expertRowNum) : startRow, 1,  10 );
             if (expertRowNum > 1){
                 startRow += expertRowNum;
             }
            startRow++;

            HSSFRow row10 = sheet.createRow(startRow);
            row10.createCell(0).setCellValue("监督人签字");
            row10.createCell(1).setCellValue("");
            createCellRangeAddress(sheet,startRow, startRow, 1,  10 );
            startRow++;
            row10.setHeight((short) (39.95 * 20));


            HSSFRow row11 = sheet.createRow(startRow);
            row11.createCell(0).setCellValue("招标人员签字");
            row11.createCell(1).setCellValue("");
           createCellRangeAddress(sheet,startRow, startRow, 1,  10 );
            startRow++;
            row11.setHeight((short) (39.95 * 20));

            setFrame(3,startRow - 1,11,sheet);


            HSSFRow row12= sheet.createRow(startRow);
            row12.createCell(0).setCellValue("日期:" + DateUtil.format(new Date(),new SimpleDateFormat("yyyy-MM-dd")));
             createCellRangeAddress(sheet,startRow, startRow, 0,  (endWidth - 1) );


            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] brray = bos.toByteArray();
            in = new ByteArrayInputStream(brray);
            UploadEntity uploadEntity = new UploadEntity();
            String url = persistenceService.updateToOss(uploadEntity, in, sheetName + ".xlsx");
            in.close();
            bos.close();
            stream.close();
            log.info("生成的url{}" + url);
            return url;

        } catch (IOException e) {
            e.printStackTrace();


        }finally {
            if (bos != null){
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (in != null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static String mergExcel(List<ReviewEvaluationFormDTO> reviewEvaluationForm,Long sectionId){
        try {

            if (CollUtil.isEmpty(reviewEvaluationForm)){
                return null;
            }
            List<ReviewEvaluationFormDTO> urlColl = reviewEvaluationForm.stream().filter(reviewEvaluation -> StrUtil.isNotEmpty(reviewEvaluation.getAnnexAddress())).collect(Collectors.toList());
            if (!CollUtil.isEqualList(reviewEvaluationForm,urlColl)){
                return null;
            }
            List<File> summaryFileList = CollUtil.list(false);
            File absoluteFile = new File("temps/");
            log.info("evaluationSummaryDetails接口参数:{}",absoluteFile);

            String filePath = absoluteFile.getAbsolutePath() + File.separator + UUID.randomUUID();
            //合成初步评审
            List<ReviewEvaluationFormDTO> evaluationFormDTOS = reviewEvaluationForm.stream().filter(reviewEvaluationFormDTO -> Enums.equals(FirstReviewItemTypeEnum.PRELIMINARY_REVIEW, reviewEvaluationFormDTO.getFirstReviewItemType())).collect(Collectors.toList());
            List<File> fileList = CollUtil.list(false);
            for (ReviewEvaluationFormDTO evaluationFormDTO : evaluationFormDTOS) {
                File file = getFile(evaluationFormDTO.getAnnexAddress(), filePath);
                fileList.add(file);
            }
            //合成excel
            ExcelOperationUtil.mergeExcel(filePath,fileList,"chubupingshenhuizongsheet.xlsx");
            //合成一个sheet
             //初步汇总excel
            File preliminaryExcel = POIUtil.mergeWorkSheet(new File(filePath + File.separator + "chubupingshenhuizongsheet.xlsx"), "chubupingshenhuizong" ,"初步评审汇总", filePath, fileList.size(),0);
            summaryFileList.add(preliminaryExcel);


            //合成详细个人
            List<ReviewEvaluationFormDTO> detailedPersonalList = reviewEvaluationForm.stream().filter(reviewEvaluationFormDTO -> Enums.equals(FirstReviewItemTypeEnum.DETAILED_REVIEW, reviewEvaluationFormDTO.getFirstReviewItemType()) && Integer.valueOf(1).equals(reviewEvaluationFormDTO.getFormType())).collect(Collectors.toList());
            fileList = CollUtil.list(false);
            for (ReviewEvaluationFormDTO evaluationFormDTO : detailedPersonalList) {
                File file = getFile(evaluationFormDTO.getAnnexAddress(), filePath);
                fileList.add(file);
            }
             //合成excel
            if (CollUtil.isNotEmpty(fileList)){
                ExcelOperationUtil.mergeExcel(filePath,fileList,"xiangxigerenduogesheet.xlsx");
                File detailedPersonalFile = POIUtil.mergeWorkSheet(new File(filePath + File.separator + "xiangxigerenduogesheet.xlsx"), "xiangxipingshengeren" ,"详细评审个人", filePath, fileList.size(),1);
                summaryFileList.add(detailedPersonalFile);
            }



            //详细汇总
            List<ReviewEvaluationFormDTO> detailedSummaryList = reviewEvaluationForm.stream().filter(reviewEvaluationFormDTO -> Enums.equals(FirstReviewItemTypeEnum.DETAILED_REVIEW, reviewEvaluationFormDTO.getFirstReviewItemType()) && Integer.valueOf(2).equals(reviewEvaluationFormDTO.getFormType())).collect(Collectors.toList());
            fileList = CollUtil.list(false);
            for (ReviewEvaluationFormDTO evaluationFormDTO : detailedSummaryList) {
                File file = getFile(evaluationFormDTO.getAnnexAddress(), filePath);
                fileList.add(file);
            }
            //合成excel
            if (CollUtil.isNotEmpty(fileList)){
                ExcelOperationUtil.mergeExcel(filePath,fileList,"xiangxipingshenhuizongsheet.xlsx");
                File detailedSummaryFile = POIUtil.mergeWorkSheet(new File(filePath + File.separator + "xiangxipingshenhuizongsheet.xlsx"), "xiangxipingshenhuizong" ,"详细评审汇总", filePath, fileList.size(),2);
                summaryFileList.add(detailedSummaryFile);
            }



            //评标价算法
            List<ReviewEvaluationFormDTO> offerList = reviewEvaluationForm.stream().filter(reviewEvaluationFormDTO -> Enums.equals(FirstReviewItemTypeEnum.QUOTATION_REVIEW, reviewEvaluationFormDTO.getFirstReviewItemType()) ).collect(Collectors.toList());
            if (CollUtil.isNotEmpty(offerList)){
                ReviewEvaluationFormDTO reviewEvaluationFormDTO = offerList.get(0);
                File file = getFile(reviewEvaluationFormDTO.getAnnexAddress(), filePath,"pingbiaojiasuanfa");
                summaryFileList.add(file);
            }
            //评分汇总
            List<ReviewEvaluationFormDTO> scoreSummaryList = reviewEvaluationForm.stream().filter(reviewEvaluationFormDTO -> Integer.valueOf (-1).equals(reviewEvaluationFormDTO.getFirstReviewItemType())).collect(Collectors.toList());
            if (CollUtil.isNotEmpty(scoreSummaryList)){
                ReviewEvaluationFormDTO reviewEvaluationFormDTO = scoreSummaryList.get(0);
                File file = getFile(reviewEvaluationFormDTO.getAnnexAddress(), filePath,"pingfenhuizong");
                summaryFileList.add(file);
            }
            //评标结论表
            List<ReviewEvaluationFormDTO> scoreConclusionList = reviewEvaluationForm.stream().filter(reviewEvaluationFormDTO -> Integer.valueOf (0).equals(reviewEvaluationFormDTO.getFirstReviewItemType())).collect(Collectors.toList());
            if (CollUtil.isNotEmpty(scoreConclusionList)){
                ReviewEvaluationFormDTO reviewEvaluationFormDTO = scoreConclusionList.get(0);
                File  file= getFile(reviewEvaluationFormDTO.getAnnexAddress(), filePath,"pingbiaojielun");
                summaryFileList.add(file);
            }
            //合成评分汇总
            ExcelOperationUtil.mergeSummaryExcel(filePath,summaryFileList,"pingfenbiaoge.xlsx");
            //设置手写板图片
            File file = new File(filePath + File.separator + "pingfenbiaoge.xlsx");
            String url = importExcelImage(file, sectionId);

            log.info("生成的汇总url:{}",url);
            //生成的临时文件 删除文件夹
            deleteFilePath(filePath);
            return url;
        } catch (IOException e) {
           e.printStackTrace();
        }
        return null;


    }

     /**
      * @param file:文件
      * @param sectionId:标段id
      * @Description:导入图片
      * @Author: zhongdl
      * @Date:2022/4/30 13:3
      * @return: {@link java.lang.String }
      **/
    public static String importExcelImage(File file,Long sectionId) throws IOException {
        PersistenceService persistenceService = SpringUtil.getBean(PersistenceService.class);
        EvaluationCommitteeClient evaluationCommitteeClient = SpringUtil.getBean(EvaluationCommitteeClient.class);
        Message<List<EvaluationCommitteeMembers>> members = evaluationCommitteeClient.listMembersBySectionId(sectionId);
        List<EvaluationCommitteeMembers> data = members.getData();
        if (CollUtil.isEmpty(data)){
            String url = persistenceService.updateToOss(new UploadEntity(),   new FileInputStream(file),  "pingfenbiaoge.xlsx");
            return url;
        }
        ByteArrayOutputStream bos = null;
        ByteArrayInputStream in = null;
        try(HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(file))) {
            //设置初步评审
            setExpertBoard(hssfWorkbook,"初步评审汇总",data);
            //详细评审个人
            setDetailedExpertBoard(hssfWorkbook,"详细评审个人",data);
            //详细汇总
            setExpertBoard(hssfWorkbook,"详细评审汇总",data);
            //报价汇总
            setExpertBoard(hssfWorkbook,"报价评审汇总",data);
            //评分汇总
            setExpertBoard(hssfWorkbook,"评分汇总",data);
            //评分结论
            setExpertBoard(hssfWorkbook,"评标结论",data);

            bos = new ByteArrayOutputStream();
            hssfWorkbook.write(bos);
            byte[] brray = bos.toByteArray();
            in = new ByteArrayInputStream(brray);
            UploadEntity uploadEntity = new UploadEntity();
            String url = persistenceService.updateToOss(uploadEntity, in, "sheetName" + ".xlsx");
            in.close();
            bos.close();
            log.info("生成的手写板url:{}" + url);
            return url;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;

    }
    /**
     * @param hssfWorkbook:
     * @param sheetName:
     * @param data:
     * @Description:设置详细个人图片
     * @Author: zhongdl
     * @Date:2022/4/30 13:35

     **/
    public static void setDetailedExpertBoard(HSSFWorkbook hssfWorkbook,String sheetName,List<EvaluationCommitteeMembers> data){
        if (CollUtil.isEmpty(data)){
            data = CollUtil.list(false);
        }
        List<EvaluationCommitteeMembers> evaluationCommitteeMembers = data.stream().filter(item -> StrUtil.isNotEmpty(item.getConfirmationImage()) && !"data:image/jpeg;base64,".equals(item.getConfirmationImage())).collect(Collectors.toList());
        HSSFSheet sheetAt = hssfWorkbook.getSheet(sheetName);
        if (sheetAt == null){
            return;
        }
        int lastRowNum = sheetAt.getLastRowNum();
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        HSSFPatriarch patriarch = sheetAt.createDrawingPatriarch();
        StringBuilder referenceStringBuilder = new StringBuilder();
        for (int i = 0; i <= lastRowNum; i++) {
            HSSFRow row = sheetAt.getRow(i);
            if(row == null){
                if ( StrUtil.isBlank(StrUtil.subAfter(referenceStringBuilder,"$",true))){
                    referenceStringBuilder.append(i);
                }
                continue;
            }
            Iterator<Cell> cellIterator = row.cellIterator();
            int columnIndex = 0;
            boolean flag = false;
            boolean rowContent = false;
            boolean rowNote = false;
            boolean projectRow = false;
            boolean lastRow = false;
            while (cellIterator.hasNext()) {
                Cell next = cellIterator.next();
                String stringCellValue = getCellValue(next);
                if (stringCellValue.contains("详细评审-")){
                    setHeaderRowStyle((HSSFCell) next,39.95,16,HorizontalAlignment.CENTER_SELECTION,VerticalAlignment.CENTER);
                    referenceStringBuilder.append(StrUtil.isBlankIfStr(referenceStringBuilder)  ? "" : ",").append("$A").append("$").append(i + 1).append(":$V$");
                    break;
                }
                if (stringCellValue.contains("招标项目名称:") || stringCellValue.contains("标段(包)名称:")){
                    projectRow = true;
                    break;
                }
                if (stringCellValue.contains("专家签字")){
                    columnIndex = next.getColumnIndex();
                    flag = true;
                    break;
                }
                if (stringCellValue.contains("备注:")){
                    rowNote = true;
                    break;
                }



                if (stringCellValue.contains("注：合格打√，不合格打×") || stringCellValue.contains("日期:")){
                    lastRow = true;
                    break;
                }
                rowContent = true;
                break;
            }
            //设置项目行标识
            if (projectRow){
                setProjectRowStyle(row,10,HorizontalAlignment.LEFT,VerticalAlignment.CENTER);
            }
            //设置内容
            if (rowContent){
                setFrame(i,i,22,sheetAt);
                setContentRowStyle(row,10,HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
            }
            //备注列
            if(rowNote){
                setFrame(i,i,22,sheetAt);
                setContentRowStyle(row,10,HorizontalAlignment.LEFT,VerticalAlignment.CENTER);
            }
            //最后一行
            if(lastRow){
                setLastRowStyle(row,10);
            }
            if (!flag){
                continue;
            }
            HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
            setFrame(i,i,22,sheetAt);
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            HSSFCell cell = row.getCell(columnIndex);
            cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
            cellStyle.setBorderRight(BorderStyle.THIN); //右边框
            String expertName = getCellValue(cell).replaceAll("专家签字：","");
            cell.setCellStyle(cellStyle);
            row.setHeight((short) 400);
            int startColum = 2;
            cell.setCellValue("专家签字：");
            for (EvaluationCommitteeMembers evaluationCommitteeMember : evaluationCommitteeMembers) {
                if (!expertName.contains(evaluationCommitteeMember.getMemberName())){
                    continue;
                }
//                cell.setCellValue(getCellValue(cell).replaceAll(expertName,""));
                String imageUrl = generateImage(evaluationCommitteeMember.getConfirmationImage());
                HSSFClientAnchor hssfClientAnchor = createHSSFClientAnchor(i, i + 1, startColum, startColum + 2);
                patriarch.createPicture(hssfClientAnchor, hssfWorkbook.addPicture(getFile2byte(new File(imageUrl)), XSSFWorkbook.PICTURE_TYPE_PNG));
                startColum += 3;

            }

        }
        if ( StrUtil.isNotBlank(referenceStringBuilder) && StrUtil.isBlank(StrUtil.subAfter(referenceStringBuilder,"$",true))){
            referenceStringBuilder.append(lastRowNum + 1);
        }
        //设置打印区域
        setPintArea(hssfWorkbook,sheetName,referenceStringBuilder.toString());
    }

    /**
     * @param hssfWorkbook:
     * @param sheetName:
     * @param data:
     * @Description:设置专家手写板
     * @Author: zhongdl
     * @Date:2022/4/30 13:36

     **/
    public static void setExpertBoard(HSSFWorkbook hssfWorkbook,String sheetName,List<EvaluationCommitteeMembers> data){
        if (CollUtil.isEmpty(data)){
            data = CollUtil.list(false);
        }
        List<EvaluationCommitteeMembers> evaluationCommitteeMembers = data.stream().filter(item -> StrUtil.isNotEmpty(item.getConfirmationImage()) && !"data:image/jpeg;base64,".equals(item.getConfirmationImage())).collect(Collectors.toList());
        HSSFSheet sheetAt = hssfWorkbook.getSheet(sheetName);
        if (sheetAt == null){
            return;
        }
        int lastRowNum = sheetAt.getLastRowNum();
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        HSSFPatriarch patriarch = sheetAt.createDrawingPatriarch();
        StringBuilder referenceStringBuilder = new StringBuilder();
        //是否是初步评审 详细评审汇总 评分汇总 报价汇总
       boolean sheetIndexFlag = "初步评审汇总".equals(sheetName)
                || "详细评审汇总".equals(sheetName)
                || "报价评审汇总".equals(sheetName)
                || "评分汇总".equals(sheetName);
        for (int i = 0; i <= lastRowNum; i++) {
            HSSFRow row = sheetAt.getRow(i);
            //打印区域

            if (row == null){
                if ( sheetIndexFlag && StrUtil.isBlank(StrUtil.subAfter(referenceStringBuilder,"$",true))){
                    referenceStringBuilder.append(i);
                }
                continue;
            }
            Iterator<Cell> cellIterator = row.cellIterator();
            int columnIndex = 0;
            boolean flag = false;
            boolean rowContent = false;
            boolean rowNote = false;
            boolean lastRow = false;
            //是否是招标项目行
            boolean projectRow = false;
            while (cellIterator.hasNext()) {
                Cell next = cellIterator.next();
                String stringCellValue = getCellValue(next);
                if (stringCellValue.contains("初步评审-")
                        || stringCellValue.contains("详细评审-")
                        || ("报价评审".equals(stringCellValue) && next.getColumnIndex() == 0)
                        || stringCellValue.contains("开标一览表")
                        || stringCellValue.contains("评分汇总表")
                        || stringCellValue.contains("评标结论表")){
                    setHeaderRowStyle((HSSFCell) next,39.95,16,HorizontalAlignment.CENTER_SELECTION,VerticalAlignment.CENTER);
                    if (sheetIndexFlag){
                        referenceStringBuilder.append(StrUtil.isBlankIfStr(referenceStringBuilder)  ? "" : ",").append("$A").append("$").append(i + 1).append(":$V$");
                    }
                    break;
                }
                if (stringCellValue.contains("招标项目名称:") || stringCellValue.contains("标段(包)名称:")){
                    projectRow = true;
                    break;
                }
                if (stringCellValue.contains("专家签字")){
                    columnIndex = next.getColumnIndex();
                    flag = true;
                    break;
                }

                if (stringCellValue.contains("备注:")){
                    rowNote = true;
                    break;
                }
                if (stringCellValue.contains("注：合格打√，不合格打×") || stringCellValue.contains("日期:")){
                    lastRow = true;
                    break;
                }
                rowContent = true;
                break;
            }

            //设置项目行标识
            if (projectRow){
                setProjectRowStyle(row,10,HorizontalAlignment.LEFT,VerticalAlignment.CENTER);
            }
            //设置内容
            if (rowContent){
                setContentRowStyle(row,10,HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
            }
            //备注列
            if(rowNote){
                setContentRowStyle(row,10,HorizontalAlignment.LEFT,VerticalAlignment.CENTER);
            }
            //最后一行
            if(lastRow){
                setLastRowStyle(row,10);
            }
            if (!flag){
                continue;
            }
            HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
            cellStyle.setBorderRight(BorderStyle.THIN); //右边框
            HSSFCell cell = row.getCell(columnIndex);

            row.setHeight((short) 400);
            //查询未有手写板的专家
            List<String> notBoardExpertName = data.stream().filter(item -> StrUtil.isEmpty(item.getConfirmationImage()) || "data:image/jpeg;base64,".equals(item.getConfirmationImage())).map(EvaluationCommitteeMembers::getMemberName).collect(Collectors.toList());
           if ("评标结论".equals(sheetName)){
               HSSFFont font = hssfWorkbook.createFont();
               cellStyle.setFont(font);
               cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
               cellStyle.setAlignment(HorizontalAlignment.CENTER);
               HSSFCell cell1 = row.getCell(0);
               cell1.setCellStyle(cellStyle);
            }

            cell.setCellStyle(cellStyle);


            int startColum = notBoardExpertName.size() + 2;
            int startRow = i;
            int size = evaluationCommitteeMembers.size();
            for (int j = 0; j < size; j++) {
                if ( ("初步评审汇总".equals(sheetName) && startColum > 19)
                    ||  ("详细评审汇总".equals(sheetName) && startColum > 19)
                    ||  ("报价评审汇总".equals(sheetName) && startColum > 11)
                    ||  ("评分汇总".equals(sheetName) && startColum > 19)
                    ||  ("评标结论".equals(sheetName) && startColum > 8)
                ){
                    startRow++;
                    startColum = notBoardExpertName.size() + 2;
                }
                EvaluationCommitteeMembers evaluationCommitteeMember = evaluationCommitteeMembers.get(j);
                String imageUrl = generateImage(evaluationCommitteeMember.getConfirmationImage());
                HSSFClientAnchor hssfClientAnchor = createHSSFClientAnchor(startRow, startRow + 1, startColum, startColum + 2);
                patriarch.createPicture(hssfClientAnchor, hssfWorkbook.addPicture(getFile2byte(new File(imageUrl)), XSSFWorkbook.PICTURE_TYPE_PNG));
                startColum += 3;
            }


        }
        if ( StrUtil.isNotBlank(referenceStringBuilder) && StrUtil.isBlank(StrUtil.subAfter(referenceStringBuilder,"$",true))){
            referenceStringBuilder.append(lastRowNum + 1);
        }
        //设置打印区域
        setPintArea(hssfWorkbook,sheetName,referenceStringBuilder.toString());

    }

    public static void setPintArea(HSSFWorkbook hssfWorkbook,String sheetName,String reference){
        HSSFSheet sheetAt = hssfWorkbook.getSheet(sheetName);
        int sheetIndex = hssfWorkbook.getSheetIndex(sheetAt);
        if ("评标结论".equals(sheetName)){
            reference = "$A$1:$K$18";
        }
        if (StrUtil.isBlank(reference)){
            return;
        }
        //第一个打印区域（该字符串表示表格的B列4行到H列11行的区域为打印区域）

            hssfWorkbook.setPrintArea(sheetIndex,reference);
            sheetAt.setAutobreaks(false);//这个是sheet缩放设置，默认打印一页要true

            sheetAt.setMargin(Sheet.BottomMargin,( double ) 0.5 );// 页边距（下）

            sheetAt.setMargin(Sheet.LeftMargin,( double ) 0.1 );// 页边距（左）

            sheetAt.setMargin(Sheet.RightMargin,( double ) 0.1 );// 页边距（右）

            sheetAt.setMargin(Sheet.TopMargin,( double ) 0.5 );// 页边距（上）

            sheetAt.setHorizontallyCenter(true);//设置打印页面为水平居中

            sheetAt.setVerticallyCenter(true);//设置打印页面为垂直居中
            sheetAt.setVerticallyCenter(true);//设置打印页面为垂直居中

            //使用POI输出Excel时打印页面

//启用“适合页面”打印选项的标志。(默认选择的是“将工作表调整为一页”)

            sheetAt.setFitToPage(false);
            HSSFPrintSetup printSetup = sheetAt.getPrintSetup();

            printSetup.setLandscape(true); // 打印方向，true：横向，false：纵向

            printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE); //纸张大小，自选A4 作者：大萌音音 https://www.bilibili.com/read/cv15053559 出处：bilibili

    }


    public static void setLastRowStyle( HSSFRow row,double fontSize){
        HSSFWorkbook workbook = row.getSheet().getWorkbook();
        HSSFCellStyle cellStyleBorderTop = workbook.createCellStyle();
        cellStyleBorderTop.setBorderTop(BorderStyle.THIN); //上边框

        Iterator<Cell> cellIterator = row.cellIterator();
        double rowHeight = 0;
        while (cellIterator.hasNext()) {
            Cell next = cellIterator.next();

            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setFontHeight((short) (20 * fontSize));
            cellStyle.setFont(font);
            font.setBold(true);
            cellStyle.setBorderTop(BorderStyle.THIN); //上边框
            cellStyle.setWrapText(true);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            if (getCellValue(next).contains("注：合格打√，不合格打×")){

                cellStyle.setAlignment(HorizontalAlignment.LEFT);

            }else  {
                cellStyle.setAlignment(HorizontalAlignment.RIGHT);

            }
            int columnIndex = next.getColumnIndex() + 1;
            int colSpan = getColSpanNumber(next, row.getSheet());
            for (int i = columnIndex; i <= colSpan; i++) {
                row.createCell(i).setCellStyle(cellStyleBorderTop);
            }
            next.setCellStyle(cellStyle);
            double height = getHeight(row, next.getColumnIndex());
            rowHeight = Math.max(height, rowHeight);
            next.setCellStyle(cellStyle);
        }
        row.setHeight( (short) (12.5 * 20));
    }

    public static void setContentRowStyle( HSSFRow row,double fontSize,HorizontalAlignment horizontalAlignment,VerticalAlignment verticalAlignment){
        HSSFWorkbook workbook = row.getSheet().getWorkbook();
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        if(horizontalAlignment != null){
            cellStyle.setAlignment(horizontalAlignment);
        }
        //设置垂直对齐方式
        if (verticalAlignment != null){
            cellStyle.setVerticalAlignment(verticalAlignment);
        }
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);

        HSSFFont font = workbook.createFont();
        font.setFontHeight((short) (20 * fontSize));
        cellStyle.setFont(font);
        Iterator<Cell> cellIterator = row.cellIterator();
        double rowHeight = 0;
        while (cellIterator.hasNext()) {
            Cell next = cellIterator.next();
            double height = getHeight(row, next.getColumnIndex());
            rowHeight = Math.max(height, rowHeight);
            next.setCellStyle(cellStyle);
        }
        row.setHeight((short) rowHeight);
    }
    public static void setProjectRowStyle( HSSFRow row,double fontSize,HorizontalAlignment horizontalAlignment,VerticalAlignment verticalAlignment){
        HSSFWorkbook workbook = row.getSheet().getWorkbook();
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        if(horizontalAlignment != null){
            cellStyle.setAlignment(horizontalAlignment);
        }
        //设置垂直对齐方式
        if (verticalAlignment != null){
            cellStyle.setVerticalAlignment(verticalAlignment);
        }
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);

        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeight((short) (20 * fontSize));
        font.setBold(true);
        cellStyle.setFont(font);
        Iterator<Cell> cellIterator = row.cellIterator();
        double rowHeight = 0;
        while (cellIterator.hasNext()) {
            Cell next = cellIterator.next();
            double height = getHeight(row, next.getColumnIndex());
            rowHeight = Math.max(height, rowHeight);
            next.setCellStyle(cellStyle);
        }
        row.setHeight((short) rowHeight);
    }
    public static void setHeaderRowStyle(HSSFCell cell,double rowHeight,double fontSize,HorizontalAlignment horizontalAlignment,VerticalAlignment verticalAlignment){
        HSSFWorkbook workbook = cell.getSheet().getWorkbook();
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        if(horizontalAlignment != null){
            cellStyle.setAlignment(horizontalAlignment);
        }
        //设置垂直对齐方式
        if (verticalAlignment != null){
            cellStyle.setVerticalAlignment(verticalAlignment);
        }
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeight((short) (fontSize * 20 ));
        font.setBold(true);
        cellStyle.setFont(font);

        cell.getRow().setHeight((short) (rowHeight * 20));
        cell.setCellStyle(cellStyle);
    }

    public static HSSFClientAnchor createHSSFClientAnchor(int startRow,int endRow,int startCol,int endCol){
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) startCol, startRow, (short) endCol, endRow);
        anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
        return anchor;

    }

    /**
     * 设置自适应行高的方法
     */
    public static double getHeight(HSSFRow row, int cellIndex){
        HSSFCell cell = row.getCell(cellIndex);
        //1.先取出内容 计算长度 （这个方法在后面...）
        String content = getCellValue(cell);
        //计算字体的高度
        HSSFCellStyle cellStyle = cell.getCellStyle();
        HSSFFont font = cellStyle.getFont(row.getSheet().getWorkbook());
        //字体的高度
        short fontHeight = font.getFontHeight();
        //计算字符的宽度
        //代入默认字符宽度8：//字符像素宽度 = 字体宽度 * 字符个数 + 边距
        //像素 = 5 + (字符个数 * 7)
        int i = 5 + (content.toCharArray().length * 7);
        //POI中的字符宽度算法是：double 宽度 = (字符个数 * (字符宽度 - 1) + 5) / (字符宽度 - 1) * 256;
//        double poiWidth = content.length() * 12 / 7 * 256;
         double poiWidth = content.getBytes().length * 2 * 256;
        //然后再四舍五入成整数。
        int width = (int) Math.ceil(poiWidth);
        //2.除以宽度 计算行数乘以行高   (256*80) 是我设置的我的单元格列的宽度
        HSSFSheet sheet = row.getSheet();
        int columnWidth = getColSpan(row.getCell(cellIndex), row.getSheet());
        double rowNum = (double) width/ (columnWidth == 0 ? (256*80) : columnWidth);
        int rowNums = (int)Math.ceil(rowNum);
        double height = (rowNums + 1.1) * fontHeight;//这个420 是我为每一行设置的高度 有点大，可以用字体的高度加一点（前面方法中有计算字体高度的方法）
        return height;//直接设置行高为这个值就OK了
    }
    /**
     * 获取cell跨的列数
     * @param cell
     * @param sheet
     * @return
     */
    public static int getColSpanNumber(Cell cell, HSSFSheet sheet) {
        int colSpan = 1;
        int columnWidth = 0;
        List<CellRangeAddress> list = sheet.getMergedRegions();
        for (CellRangeAddress cellRangeAddress : list) {
            if (cellRangeAddress.isInRange(cell)) {
                int lastColumn = cellRangeAddress.getLastColumn();
                return lastColumn;
            }
        }
        return columnWidth;
    }


    /**
     * 获取cell跨的列数
     * @param cell
     * @param sheet
     * @return
     */
    public static int getColSpan(Cell cell, HSSFSheet sheet) {
        int colSpan = 1;
        int columnWidth = 0;
        List<CellRangeAddress> list = sheet.getMergedRegions();
        for (CellRangeAddress cellRangeAddress : list) {
            if (cellRangeAddress.isInRange(cell)) {
                int lastColumn = cellRangeAddress.getLastColumn() + 1;
                int firstColumn = cellRangeAddress.getFirstColumn();
                colSpan = cellRangeAddress.getLastColumn() - cellRangeAddress.getFirstColumn() + 1; // +1是因为如果没跨，就算1
                for (int i = firstColumn; i < lastColumn; i++) {
                    columnWidth += sheet.getColumnWidth(i);
                }
                break;
            }
        }
        return columnWidth;
    }

    public static String getCellValue(Cell next){
        String value = "";
        switch (next.getCellType()){
            case STRING:
                value = next.getStringCellValue();
                break;
            case NUMERIC:
                value = next.getNumericCellValue() + "";
                break;
        }

        return value;

    }



    public static void setImage(HSSFWorkbook xssfWorkbook,String confirmationImagePath) {
        FileOutputStream fileOmageut = null;
        BufferedImage bufferImg = null;
        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        try {
            File file = new File(confirmationImagePath);

//            ImageIO.write(bufferImg, "png", byteArrayOut);

            HSSFSheet sheet1 = xssfWorkbook.getSheetAt(0);
            HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
            //anchor主要用于设置图片的属性

            /**
             * @param dx1 图片的左上角在开始单元格（col1,row1）中的横坐标
             * @param dy1 图片的左上角在开始单元格（col1,row1）中的纵坐标
             * @param dx2 图片的右下角在结束单元格（col2,row2）中的横坐标
             * @param dy2 图片的右下角在结束单元格（col2,row2）中的纵坐标
             * @param col1 开始单元格所处的列号, base 0, 图片左上角在开始单元格内
             * @param row1 开始单元格所处的行号, base 0, 图片左上角在开始单元格内
             * @param col2 结束单元格所处的列号, base 0, 图片右下角在结束单元格内
             * @param row2 结束单元格所处的行号, base 0, 图片右下角在结束单元格内
             */
            //XSSFClientAnchor anchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2,(short) col1, row1, (short) col2, row2);
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 9, 9, (short) 12, 8);
            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
            //插入图片
            patriarch.createPicture(anchor, xssfWorkbook.addPicture(getFile2byte(file), XSSFWorkbook.PICTURE_TYPE_PNG));
            HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 0, 0,(short) 13, 9, (short) 17, 8);
            anchor1.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
            patriarch.createPicture(anchor1, xssfWorkbook.addPicture(getFile2byte(file), XSSFWorkbook.PICTURE_TYPE_PNG));
            // 写入excel文件
            System.out.println("----Excle文件已生成------");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
           if (fileOmageut != null){
               try {
                   fileOmageut.close();
               } catch (IOException e) {
                   e.printStackTrace();
               }
           }

        }
    }

    /**
     * 删除文件，可以是文件或文件夹
     *
     * @param fileName：要删除的文件名
     * @return 删除成功返回true，否则返回false
     */
    public static boolean deleteFilePath(String fileName) {
        File file = new File(fileName);
        if (!file.exists()) {
            return false;
        } else {
            if (file.isFile()) {
                return deleteFile(fileName);
            }
            else {
                return deleteDirectory(fileName);
            }
        }
    }

    /**
     * 删除单个文件
     *
     * @param fileName：要删除的文件的文件名
     * @return 单个文件删除成功返回true，否则返回false
     */
    public static boolean deleteFile(String fileName) {
        File file = new File(fileName);
        // 如果文件路径所对应的文件存在，并且是一个文件，则直接删除
        if (file.exists() && file.isFile()) {
            if (file.delete()) {
                log.info("删除单个文件" + fileName + "成功！");
                return true;
            } else {
                log.info("删除单个文件" + fileName + "失败！");
                return false;
            }
        } else {
            System.out.println("删除单个文件失败：" + fileName + "不存在！");
            return false;
        }
    }

    /**
     * 删除目录及目录下的文件
     *
     * @param dir：要删除的目录的文件路径
     * @return 目录删除成功返回true，否则返回false
     */
    public static boolean deleteDirectory(String dir) {
        // 如果dir不以文件分隔符结尾，自动添加文件分隔符
        if (!dir.endsWith(File.separator)) {
            dir = dir + File.separator;
        }
        File dirFile = new File(dir);
        // 如果dir对应的文件不存在，或者不是一个目录，则退出
        if ((!dirFile.exists()) || (!dirFile.isDirectory())) {
            return false;
        }
        boolean flag = true;
        // 删除文件夹中的所有文件包括子目录
        File[] files = dirFile.listFiles();
        for (int i = 0; i < files.length; i++) {
            // 删除子文件
            if (files[i].isFile()) {
                flag = deleteFile(files[i].getAbsolutePath());
                if (!flag) {
                    break;
                }
            }
            // 删除子目录
            else if (files[i].isDirectory()) {
                flag = deleteDirectory(files[i].getAbsolutePath());
                if (!flag) {
                    break;
                }
            }
        }
        if (!flag) {
            return false;
        }
        // 删除当前目录
        if (dirFile.delete()) {
            return true;
        } else {
            return false;
        }
    }
    public static File getFile(String url,String path,String fileName) throws IOException {
        OutputStream os = null;
        int len;
        HttpRequest httpRequest = HttpRequest.get(url);
        HttpResponse execute = httpRequest.execute();
        byte[] bytes = execute.bodyBytes();
        InputStream inputStream = new ByteArrayInputStream(bytes);

        File tempFile = new File(path);
        if (!tempFile.exists()) {
            tempFile.mkdirs();
        }
        String filePath = tempFile.getPath() + File.separator + fileName + ".xlsx";
        os = new FileOutputStream(filePath);
        // 开始读取
        while ((len = inputStream.read(bytes)) != -1) {
            os.write(bytes, 0, len);
        }
        os.close();
        return new File(filePath);
    }

    public static File getFile(String url,String path) throws IOException {
        OutputStream os = null;
        int len;
        HttpRequest httpRequest = HttpRequest.get(url);
        HttpResponse execute = httpRequest.execute();
        byte[] bytes = execute.bodyBytes();
        InputStream inputStream = new ByteArrayInputStream(bytes);

        File tempFile = new File(path);
        if (!tempFile.exists()) {
            tempFile.mkdirs();
        }
        String filePath = tempFile.getPath() + File.separator + UUID.randomUUID().toString() + ".xlsx";
        os = new FileOutputStream(filePath);
        // 开始读取
        while ((len = inputStream.read(bytes)) != -1) {
            os.write(bytes, 0, len);
        }
        os.close();
        return new File(filePath);
    }
}
